/**
 * 菜单数据模型
 * 负责处理菜单相关的数据库操作
 * 包括菜单的增删改查、树形结构构建等功能
 */
import { pool } from "../config/database.js";


class Menu {
	/**
	 * 创建新菜单
	 * @param {Object} menuData - 菜单数据对象
	 * @param {number} [menuData.parent_id=0] - 父菜单ID，默认为0表示一级菜单
	 * @param {string} menuData.name - 菜单名称
	 * @param {number} menuData.menu_type - 菜单类型(1:一级菜单,2:二级菜单,3:三级菜单,4:组件路由,5:权限控制)
	 * @param {string} [menuData.path] - 路由路径，权限控制类型可为空
	 * @param {string} [menuData.component] - 组件路径，权限控制类型可为空
	 * @param {string} [menuData.icon] - 图标，权限控制类型可为空
	 * @param {number} [menuData.sort=0] - 排序值，默认为0
	 * @param {number} [menuData.status=1] - 状态(0:禁用,1:启用)，默认为1
	 * @returns {Promise<Object>} 创建的菜单对象，包含生成的ID
	 * @throws {Error} 数据库操作错误
	 */
	static async create(menuData) {
		try {
			const [result] = await pool.execute(
				"INSERT INTO menus (parent_id, name, menu_type, path, component, icon, sort, status, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW())",
				[
					menuData.parent_id || 0,
					menuData.name,
					menuData.menu_type,
					menuData.path || null,
					menuData.component || null,
					menuData.icon || null,
					menuData.sort || 0,
					menuData.status === undefined ? 1 : menuData.status,
				]
			);
			return { id: result.insertId, ...menuData };
		} catch (error) {
			throw error;
		}
	}

	/**
	 * 通过ID查找菜单
	 * @param {number} id - 菜单ID
	 * @returns {Promise<Object|null>} 菜单对象，如果不存在返回null
	 * @throws {Error} 数据库操作错误
	 */
	static async findById(id) {
		try {
			const [rows] = await pool.execute(
				"SELECT id, parent_id, name, menu_type, path, component, icon, sort, status, created_at, updated_at FROM menus WHERE id = ? AND is_deleted = 0",
				[id]
			);
			return rows[0];
		} catch (error) {
			throw error;
		}
	}

	/**
	 * 获取指定父菜单下的子菜单列表
	 * @param {number} parentId - 父菜单ID
	 * @returns {Promise<Array>} 子菜单数组
	 * @throws {Error} 数据库操作错误
	 */
	static async findChildren(parentId) {
		try {
			const [rows] = await pool.execute(
				"SELECT id, parent_id, name, menu_type, path, component, icon, sort, status, created_at, updated_at FROM menus WHERE parent_id = ? AND is_deleted = 0 ORDER BY sort ASC",
				[parentId]
			);
			return rows;
		} catch (error) {
			throw error;
		}
	}

	/**
	 * 检查指定菜单是否有子菜单
	 * @param {number} menuId - 菜单ID
	 * @returns {Promise<boolean>} 如果有子菜单返回true，否则返回false
	 * @throws {Error} 数据库操作错误
	 */
	static async hasChildren(menuId) {
		try {
			const [rows] = await pool.execute(
				"SELECT COUNT(*) as count FROM menus WHERE parent_id = ? AND is_deleted = 0",
				[menuId]
			);
			return rows[0].count > 0;
		} catch (error) {
			throw error;
		}
	}

	/**
	 * 更新菜单信息
	 * 只更新传入的字段，未传入的字段保持不变
	 * 如果修改menu_type为5(权限控制)，自动清空path、component和icon字段
	 *
	 * @param {number} id - 菜单ID
	 * @param {Object} menuData - 需要更新的菜单数据
	 * @param {number} [menuData.parent_id] - 父菜单ID
	 * @param {string} [menuData.name] - 菜单名称
	 * @param {number} [menuData.menu_type] - 菜单类型
	 * @param {string} [menuData.path] - 路由路径
	 * @param {string} [menuData.component] - 组件路径
	 * @param {string} [menuData.icon] - 图标
	 * @param {number} [menuData.sort] - 排序值
	 * @param {number} [menuData.status] - 状态(0:禁用,1:启用)
	 * @returns {Promise<boolean>} 更新成功返回true，否则返回false
	 * @throws {Error} 数据库操作错误
	 */
	static async update(id, menuData) {
		try {
			const updateFields = [];
			const updateValues = [];

			// 动态构建更新字段
			if (menuData.parent_id !== undefined) {
				updateFields.push("parent_id = ?");
				updateValues.push(menuData.parent_id);
			}
			if (menuData.name !== undefined) {
				updateFields.push("name = ?");
				updateValues.push(menuData.name);
			}
			if (menuData.menu_type !== undefined) {
				updateFields.push("menu_type = ?");
				updateValues.push(menuData.menu_type);

				// 如果menu_type为5(权限控制)，清空path、component和icon
				if (menuData.menu_type === 5) {
					updateFields.push("path = NULL, component = NULL, icon = NULL");
				}
			}
			if (menuData.path !== undefined && menuData.menu_type !== 5) {
				updateFields.push("path = ?");
				updateValues.push(menuData.path);
			}
			if (menuData.component !== undefined && menuData.menu_type !== 5) {
				updateFields.push("component = ?");
				updateValues.push(menuData.component);
			}
			if (menuData.icon !== undefined && menuData.menu_type !== 5) {
				updateFields.push("icon = ?");
				updateValues.push(menuData.icon);
			}
			if (menuData.sort !== undefined) {
				updateFields.push("sort = ?");
				updateValues.push(menuData.sort);
			}
			if (menuData.status !== undefined) {
				updateFields.push("status = ?");
				updateValues.push(menuData.status);
			}

			// 添加更新时间
			updateFields.push("updated_at = NOW()");

			// 添加ID参数到查询条件
			updateValues.push(id);

			// 构建并执行UPDATE语句
			const [result] = await pool.execute(
				`UPDATE menus SET ${updateFields.join(
					", "
				)} WHERE id = ? AND is_deleted = 0`,
				updateValues
			);

			// 返回是否有记录被更新
			return result.affectedRows > 0;
		} catch (error) {
			throw error;
		}
	}

	/**
	 * 软删除菜单（将is_deleted标记为1）
	 * @param {number} id - 菜单ID
	 * @returns {Promise<boolean>} 删除成功返回true，否则返回false
	 * @throws {Error} 数据库操作错误
	 */
	static async softDelete(id) {
		let connection;
		try {
			// 获取连接
			connection = await pool.getConnection();
			
			// 开始事务
			await connection.beginTransaction();
			
			// 软删除菜单
			const [result] = await connection.execute(
				"UPDATE menus SET is_deleted = 1, updated_at = NOW() WHERE id = ? AND is_deleted = 0",
				[id]
			);
			
			// 删除角色菜单关联记录
			await connection.execute(
				"DELETE FROM role_menus WHERE menu_id = ?",
				[id]
			);
			
			// 提交事务
			await connection.commit();
			
			return result.affectedRows > 0;
		} catch (error) {
			// 发生错误时回滚事务
			if (connection) {
				await connection.rollback();
			}
			throw error;
		} finally {
			// 释放连接
			if (connection) {
				connection.release();
			}
		}
	}

	/**
	 * 递归软删除菜单及其所有子菜单
	 * 使用事务确保数据一致性
	 * 同时删除角色菜单关联记录
	 *
	 * @param {number} id - 菜单ID
	 * @returns {Promise<boolean>} 删除成功返回true
	 * @throws {Error} 数据库操作错误时抛出异常并回滚事务
	 */
	static async recursiveSoftDelete(id) {
		let connection;
		try {
			// 获取连接
			connection = await pool.getConnection();

			// 开始事务
			await connection.beginTransaction();

			// 获取子菜单
			const [children] = await connection.execute(
				"SELECT * FROM menus WHERE parent_id = ? AND is_deleted = 0",
				[id]
			);

			// 递归删除子菜单
			for (const child of children) {
				// 获取子菜单的子菜单
				const [grandchildren] = await connection.execute(
					"SELECT * FROM menus WHERE parent_id = ? AND is_deleted = 0",
					[child.id]
				);

				// 删除子菜单的子菜单
				for (const grandchild of grandchildren) {
					await connection.execute(
						"UPDATE menus SET is_deleted = 1, updated_at = NOW() WHERE id = ? AND is_deleted = 0",
						[grandchild.id]
					);

					// 删除角色菜单关联记录 
					await connection.execute(
						"DELETE FROM role_menus WHERE menu_id = ?",
						[grandchild.id]
					);
				}

				// 软删除子菜单
				await connection.execute(
					"UPDATE menus SET is_deleted = 1, updated_at = NOW() WHERE id = ? AND is_deleted = 0",
					[child.id]
				);

				// 删除角色菜单关联记录 
				await connection.execute(
					"DELETE FROM role_menus WHERE menu_id = ?",
					[child.id]
				);
			}

			// 软删除当前菜单
			await connection.execute(
				"UPDATE menus SET is_deleted = 1, updated_at = NOW() WHERE id = ? AND is_deleted = 0",
				[id]
			);

			// 删除角色菜单关联记录
			await connection.execute("DELETE FROM role_menus WHERE menu_id = ?", [
				id,
			]);

			// 提交事务
			await connection.commit();

			return true;
		} catch (error) {
			// 发生错误时回滚事务
			if (connection) {
				await connection.rollback();
			}
			throw error;
		} finally {
			// 释放连接
			if (connection) {
				connection.release();
			}
		}
	}

	/**
	 * 获取所有未删除的菜单
	 * 主要用于构建树形结构
	 *
	 * @returns {Promise<Array>} 菜单数组，按排序值升序排列
	 * @throws {Error} 数据库操作错误
	 */
	static async findAll() {
		try {
			const [rows] = await pool.execute(
				"SELECT id, parent_id, name, menu_type, path, component, icon, sort, status, created_at, updated_at FROM menus WHERE is_deleted = 0 ORDER BY sort ASC"
			);
			return rows;
		} catch (error) {
			throw error;
		}
	}

	/**
	 * 获取指定角色可访问的菜单列表
	 * 只返回未删除且启用状态的菜单
	 *
	 * @param {number} roleId - 角色ID
	 * @returns {Promise<Array>} 菜单数组，按菜单类型和排序值升序排列
	 * @throws {Error} 数据库操作错误
	 */
	static async findMenusByRoleId(roleId) {
		try {
			const [rows] = await pool.execute(
				`SELECT m.id, m.parent_id, m.name, m.menu_type, m.path, m.component, m.icon, m.sort, m.status, m.created_at, m.updated_at FROM menus m
                JOIN role_menus rm ON m.id = rm.menu_id
                WHERE rm.role_id = ? AND m.is_deleted = 0 AND m.status = 1
                ORDER BY m.menu_type, m.sort ASC`,
				[roleId]
			);
			return rows;
		} catch (error) {
			throw error;
		}
	}

	/**
	 * 分页查询菜单列表
	 * 支持多种过滤条件
	 *
	 * @param {number} [page=1] - 页码，从1开始
	 * @param {number} [pageSize=10] - 每页条数
	 * @param {Object} [filters={}] - 过滤条件
	 * @param {string} [filters.name] - 按菜单名称模糊搜索
	 * @param {number} [filters.status] - 按状态过滤
	 * @param {number} [filters.menu_type] - 按菜单类型过滤
	 * @param {number} [filters.parent_id] - 按父菜单ID过滤
	 * @returns {Promise<Object>} 分页结果对象，包含数据和分页信息
	 * @returns {Array} result.data - 菜单数据数组
	 * @returns {Object} result.pagination - 分页信息
	 * @returns {number} result.pagination.total - 总记录数
	 * @returns {number} result.pagination.page - 当前页码
	 * @returns {number} result.pagination.pageSize - 每页条数
	 * @returns {number} result.pagination.totalPages - 总页数
	 * @returns {boolean} result.pagination.hasNextPage - 是否有下一页
	 * @returns {boolean} result.pagination.hasPrevPage - 是否有上一页
	 * @throws {Error} 数据库操作错误
	 * @deprecated 对于菜单这种层级结构，推荐使用 findMenuTree 方法获取完整的树形结构
	 */
	static async findWithPagination(page = 1, pageSize = 10, filters = {}) {
		try {
			// 构建基础查询
			let query =
				"SELECT id, parent_id, name, menu_type, path, component, icon, sort, status, created_at, updated_at FROM menus WHERE is_deleted = 0";
			let countQuery =
				"SELECT COUNT(*) as total FROM menus WHERE is_deleted = 0";
			const queryParams = [];

			// 添加过滤条件
			if (filters.name) {
				query += " AND name LIKE ?";
				countQuery += " AND name LIKE ?";
				queryParams.push(`%${filters.name}%`);
			}

			if (filters.status !== undefined) {
				query += " AND status = ?";
				countQuery += " AND status = ?";
				queryParams.push(filters.status);
			}

			if (filters.menu_type !== undefined) {
				query += " AND menu_type = ?";
				countQuery += " AND menu_type = ?";
				queryParams.push(filters.menu_type);
			}

			if (filters.parent_id !== undefined) {
				query += " AND parent_id = ?";
				countQuery += " AND parent_id = ?";
				queryParams.push(filters.parent_id);
			}

			// 添加排序
			query += " ORDER BY sort ASC";

			// 计算分页参数
			const offset = (page - 1) * pageSize;
			query += " LIMIT ? OFFSET ?";

			// 添加分页参数
			const paginationParams = [...queryParams];
			paginationParams.push(pageSize, offset);

			// 执行数据查询和总数查询
			const [rows] = await pool.execute(query, paginationParams);
			const [totalResult] = await pool.execute(countQuery, queryParams);
			const total = totalResult[0].total;

			// 计算总页数
			const totalPages = Math.ceil(total / pageSize);

			// 返回分页结果
			return {
				data: rows,
				pagination: {
					total,
					page,
					pageSize,
					totalPages,
					hasNextPage: page < totalPages,
					hasPrevPage: page > 1,
				},
			};
		} catch (error) {
			throw error;
		}
	}

	/**
	 * 获取完整的菜单树结构
	 * 适用于具有层级关系的菜单数据，保留完整的父子关系
	 *
	 * @param {Object} [filters={}] - 过滤条件
	 * @param {string} [filters.name] - 按菜单名称模糊搜索
	 * @param {number} [filters.status] - 按状态过滤
	 * @param {number} [filters.menu_type] - 按菜单类型过滤
	 * @returns {Promise<Array>} 树形结构的菜单数组，一级菜单作为顶层节点
	 * @throws {Error} 数据库操作错误
	 */
	static async findMenuTree(filters = {}) {
		try {
			// 构建基础查询
			let query = "SELECT id, parent_id, name, menu_type, path, component, icon, sort, status, created_at, updated_at FROM menus WHERE is_deleted = 0";
			const queryParams = [];

			// 添加过滤条件
			if (filters.name) {
				query += " AND name LIKE ?";
				queryParams.push(`%${filters.name}%`);
			}

			if (filters.status !== undefined) {
				query += " AND status = ?";
				queryParams.push(filters.status);
			}

			if (filters.menu_type !== undefined) {
				query += " AND menu_type = ?";
				queryParams.push(filters.menu_type);
			}

			// 按菜单类型和排序值排序
			query += " ORDER BY menu_type, sort ASC";

			// 获取所有符合条件的菜单
			const [allMenus] = await pool.execute(query, queryParams);

			// 构建树形结构
			const menuMap = {};
			const menuTree = [];

			// 第一步：将所有菜单放入映射表中，以ID为键
			allMenus.forEach(menu => {
				// 初始化children数组
				menu.children = [];
				menuMap[menu.id] = menu;
			});

			// 第二步：构建树形结构
			allMenus.forEach(menu => {
				if (menu.parent_id === 0) {
					// 顶级菜单直接添加到结果数组
					menuTree.push(menu);
				} else {
					// 非顶级菜单添加到其父菜单的children数组
					const parent = menuMap[menu.parent_id];
					if (parent) {
						parent.children.push(menu);
					}
				}
			});

			// 第三步：处理空的children数组
			const removeEmptyChildren = menus => {
				menus.forEach(menu => {
					if (menu.children.length === 0) {
						delete menu.children;
					} else {
						removeEmptyChildren(menu.children);
					}
				});
			};

			removeEmptyChildren(menuTree);

			return menuTree;
		} catch (error) {
			throw error;
		}
	}

	/**
	 * 根据菜单类型查询菜单列表
	 * @param {number} menuType - 菜单类型
	 * @returns {Promise<Array>} 菜单数组
	 */
	static async findByType(menuType) {
		try {
			const [rows] = await pool.execute(
				"SELECT id, parent_id, name, menu_type, path, component, icon, sort, status, created_at, updated_at FROM menus WHERE menu_type = ? AND is_deleted = 0 ORDER BY sort ASC",
				[menuType]
			);
			return rows;
		} catch (error) {
			throw error;
		}
	}

	/**
	 * 检查菜单名称是否已存在
	 * @param {string} name - 菜单名称
	 * @param {number} [excludeId] - 要排除的菜单ID
	 * @returns {Promise<boolean>} 是否存在
	 */
	static async isNameExist(name, excludeId) {
		try {
			let query =
				"SELECT COUNT(*) as count FROM menus WHERE name = ? AND is_deleted = 0";
			const params = [name];

			if (excludeId) {
				query += " AND id != ?";
				params.push(excludeId);
			}

			const [rows] = await pool.execute(query, params);
			return rows[0].count > 0;
		} catch (error) {
			throw error;
		}
	}
	/**
	 * 批量验证菜单ID是否存在
	 * @param {Array<number>} menuIds - 菜单ID数组
	 * @returns {Promise<Array<number>>} 不存在的菜单ID数组
	 * @throws {Error} 数据库操作错误
	 */
	static async validateMenuIds(menuIds) {
		try {
			if (!menuIds || menuIds.length === 0) {
				return [];
			}

			// 将菜单ID数组转换为逗号分隔的字符串
			const menuIdsStr = menuIds.join(",");

			// 查询存在的菜单ID
			const [rows] = await pool.execute(
				`SELECT id FROM menus WHERE id IN (${menuIdsStr}) AND is_deleted = 0`
			);

			// 获取存在的菜单ID数组
			const existingMenuIds = rows.map(row => row.id);

			// 找出不存在的菜单ID
			const nonExistingMenuIds = menuIds.filter(
				id => !existingMenuIds.includes(id)
			);

			return nonExistingMenuIds;
		} catch (error) {
			throw error;
		}
	}
}

export default Menu;
